package com.twodudesdev.amerikick.learnkarate;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
	public static final String KEY_ROWID = "_id";
	public static final String TITLE="title";
	public static final String ADR="adr";
	public static final String CTY="cty";
	public static final String ST="st";
	public static final String ZIP="zip";
	public static final String PHONE="phone";
	public static final String FAX="fax";
	public static final String EMAIL="email";
	public static final String WEBSITE="website";
	public static final String MYSCHOOL="myschool";
	public static final String USERID="userid";
	public static final String USERNAME="username";
	public static final String REALNAME="realname";
	public static final String NSID="nsid";
	
	public static final String DESC = "desc";
	public static final String ID = "id";
	public static final String ACTIVE = "active";
	public static final String START_DT = "start_dt";
	public static final String STOP_DT = "stop_dt";
	public static final String VIDEO_CATEGORY_KEY = "video_category_key";
    
    private static final String LOCATION_TABLE = "location";
    private static final String CATEGORY_TABLE = "category";
    private static final String VIDEO_TABLE = "videos";
    private static final String USERS_TABLE = "users";
    
    private final Context context; 
    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;
	
    public DBAdapter(Context ctx, String name, CursorFactory factory, int version) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context, name, factory, version);
    }
     
	public class DatabaseHelper extends SQLiteOpenHelper {
	
		//The Android's default system path of your application database.
	    private static final String DB_PATH = "/data/data/com.twodudesdev.amerikick.learnkarate/databases/";
	 
	    public static final String DB_NAME = "com.twodudesdev.amerikick.learnkarate.db";
	 
	    private static final String TAG = "DBAdapter";
	    
	    // Please, please please. If you update the database version, check to make sure the
	    // database gets upgraded properly. At a minimum, please confirm that 'upgradeVersion'
	    // is properly propagated through your change.  Not doing so will result in a loss of user
	    // settings.
	    public static final int DATABASE_VERSION = 4;
	    
	    private SQLiteDatabase myDataBase; 
	 
	    private final Context myContext;
	 
	    /**
         *      Use superconstructor for this database helper. Uses parameters usually required for this kind of helpers.
         * 
         * @param context
         * @param name
         * @param factory
         * @param version
         */
        public DatabaseHelper(Context context, String name, CursorFactory factory, int version) {
        	super(context, name, factory, version);
            this.myContext = context;
	    }	
	 
	   /**
	     * Creates a empty database on the system and rewrites it with your own database.
	     * */
	    public void createDataBase() throws IOException{
	 
	    	boolean dbExist = checkDataBase();
	 
	    	if(dbExist){
	    		//do nothing - database already exist
	    	}else{
	 
	    		//By calling this method and empty database will be created into the default system path
	               //of your application so we are gonna be able to overwrite that database with our database.
	        	this.getReadableDatabase();
	 
	        	try {
	 
	    			copyDataBase();
	 
	    		} catch (IOException e) {
	 
	        		throw new Error("Error copying database");
	 
	        	}
	    	}
	 
	    }
	 
	    /**
	     * Check if the database already exist to avoid re-copying the file each time you open the application.
	     * @return true if it exists, false if it doesn't
	     */
	    private boolean checkDataBase(){
	 
	    	SQLiteDatabase checkDB = null;
	 
	    	try{
	    		String myPath = DB_PATH + DB_NAME;
	    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
	 
	    	}catch(SQLiteException e){
	 
	    		//database does't exist yet.
	 
	    	}
	 
	    	if(checkDB != null){
	 
	    		checkDB.close();
	 
	    	}
	 
	    	return checkDB != null ? true : false;
	    }
	 
	    /**
	     * Copies your database from your local assets-folder to the just created empty database in the
	     * system folder, from where it can be accessed and handled.
	     * This is done by transfering bytestream.
	     * */
	    private void copyDataBase() throws IOException{
	 
	    	//Open your local db as the input stream
	    	InputStream myInput = myContext.getAssets().open(DB_NAME);
	 
	    	// Path to the just created empty db
	    	String outFileName = DB_PATH + DB_NAME;
	 
	    	//Open the empty db as the output stream
	    	OutputStream myOutput = new FileOutputStream(outFileName);
	 
	    	//transfer bytes from the inputfile to the outputfile
	    	byte[] buffer = new byte[1024];
	    	int length;
	    	while ((length = myInput.read(buffer))>0){
	    		myOutput.write(buffer, 0, length);
	    	}
	 
	    	//Close the streams
	    	myOutput.flush();
	    	myOutput.close();
	    	myInput.close();
	 
	    }
	    
	    private void deleteDataBase() throws IOException {
	    	String strDBFilePath= DB_PATH + DB_NAME;
		    File file = new   File(strDBFilePath);
		    file.delete();
	    }
	    
	   
	    public SQLiteDatabase openDataBase() throws SQLException{
	 
	    	//Open the database
	        //String myPath = DB_PATH + DB_NAME;
	    	myDataBase = this.getWritableDatabase(); //SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
	    	return myDataBase;
	    }
	    
	    @Override
		public synchronized void close() {
	 
	    	    if(myDataBase != null)
	    		    myDataBase.close();
	 
	    	    super.close();
	 
		}
		
	    @Override
		public void onCreate(SQLiteDatabase db) {
	 
		}
	 
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			
			Log.w(TAG, "Upgrading settings database from version " + oldVersion + " to "
					 + newVersion);
					 
			int upgradeVersion = oldVersion;
			/*
			// Pattern for upgrade blocks:
			//
			//    if (upgradeVersion == [the DATABASE_VERSION you set] - 1) {
			//        .. your upgrade logic..
			//        upgradeVersion = [the DATABASE_VERSION you set]
			//    }
			
			if (upgradeVersion == 10) {
				upgradeVersion = 11;
			}

			// *** Remember to update DATABASE_VERSION above!
			
			if (upgradeVersion != newVersion) {
				Log.w(TAG, "Got stuck trying to upgrade from version " + upgradeVersion 
						+ ", must wipe the database");
				
				db.execSQL("DROP TABLE IF EXISTS Videos");
				db.execSQL("DROP INDEX IF EXISTS Location");
				db.execSQL("DROP INDEX IF EXISTS Flickr");
				db.execSQL("DROP TABLE IF EXISTS Belt");
				db.execSQL("DROP TABLE IF EXISTS Category");
				onCreate(db);
			}
			*/
			if (newVersion > oldVersion) {
				db.beginTransaction();
				 
				boolean success = true;
				
				try {
					deleteDataBase();
					copyDataBase();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					success = false;
				}
//				for (int i = oldVersion ; i < newVersion ; ++i) {
//					int nextVersion = i + 1;
//					switch (nextVersion) {
//						case 2:
//							success = upgradeToVersion2(db);
//							break;
//						case 3:
//							success = upgrateToVersion3(db);
//							break;
						// etc. for later versions.
//					}
//					 
//					if (!success) {
//						break;
//					}
//				}
				 
				if (success) {
				db.setTransactionSuccessful();
				}
				db.endTransaction();
			}
			else {
				onCreate(db);
			}
		}
	}
	
	//---opens the database---
    public DBAdapter open() throws SQLException 
    {
        //db = DBHelper.getWritableDatabase();
        
        //DatabaseHelper myDbHelper = new DatabaseHelper();
        //myDbHelper = new DatabaseHelper(this);
 
	    try {
	    	DBHelper.createDataBase();
	    } catch (IOException ioe) {
	 		throw new Error("Unable to create database");
	 	}
	 
	 	try {
	 		db = DBHelper.openDataBase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
        
        return this;
    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }
    
    /**
     * Insert a school into the database
     *
     * @param adr The school's Address.
     * @param cty The school's City.
     * @param st The school's State.
     * @param zip The school's Zip.
     * @param phone The school's Phone Number.
     * @param fax The school's Fax Number.
     * @param email The school's email.
     * @param website The school's website.
     * @param title The school's title.
     * @param userid The foreign key to the users table.
     * @return true if the insert was successful, false if the insert failed.
     */
    public long insertSchool(String adr, String cty, String st, String zip, String phone, String fax,
    		String email, String website, String title, Integer userid) 
    {
        ContentValues initialValues = new ContentValues();
    	initialValues.put(ADR, adr);
    	initialValues.put(CTY, cty);
    	initialValues.put(ST, st);
    	initialValues.put(ZIP, zip);
    	initialValues.put(PHONE, phone);
    	initialValues.put(FAX, fax);
    	initialValues.put(EMAIL, email);
    	initialValues.put(WEBSITE, website);
    	initialValues.put(TITLE, title);
    	initialValues.put(MYSCHOOL, 0);
    	initialValues.put(USERID, userid);
    	
    	return db.insert(LOCATION_TABLE, null, initialValues);
    }

    /**
     * deletes a particular title---
     * 
     * @param rowId The Location ID to delete.
     * @return true if the delete was successful, false if it failed
     */
    public boolean deleteSchool(long rowId) 
    {
        return db.delete(LOCATION_TABLE, KEY_ROWID + 
        		"=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor object containing the Videos for a given Category
     * 
     * @param catID The category ID to return
     * @return A Cursor object with the results of the query
     * @throws SQLException
     */
    public Cursor getVidsByCat(int catID) throws SQLException
    {
    	StringBuilder sb = new StringBuilder("video_category_key =");
    	DatabaseUtils.appendEscapedSQLString(sb, String.valueOf(catID));
    	
    	Cursor mCursor =
    			db.rawQuery("SELECT " +KEY_ROWID+","+DESC+","+ID+" " +
    					"FROM " + VIDEO_TABLE + 
    					" WHERE "+VIDEO_CATEGORY_KEY + "="+ catID +
    					" AND "+START_DT+"<= strftime('%s', 'now')"+
    					" AND "+STOP_DT+"> strftime('%s', 'now')",null);
              /*  db.query(true, VIDEO_TABLE, new String[] {
                		KEY_ROWID,
                		DESC,
                		ID
                		},
                		VIDEO_CATEGORY_KEY + "=" + catID, 
                		null,
                		null,
                		null, 
                		null, 
                		null);*/
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    /**
     * ---retrieves a list of states---
     * @return A Cursor object with the results of the query
     * @throws SQLException
     */
    public Cursor getStates() throws SQLException 
    {
        Cursor mCursor =
                db.query(true, LOCATION_TABLE, new String[] {
                		KEY_ROWID,
                		ST
                		}, 
                		null, 
                		null,
                		ST, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

  /**
   * ---retrieves a list of cities within a state---
   * @param state The state to query
   * @return A Cursor object with the results of the query
   * @throws SQLException
   */
    public Cursor getCities(String state) throws SQLException 
    {
    	StringBuilder sb = new StringBuilder("st=");
    	DatabaseUtils.appendEscapedSQLString(sb, state);
        Cursor mCursor =
                db.query(true, LOCATION_TABLE, new String[] {
                		KEY_ROWID,
                		CTY
                		}, 
                		sb.toString(), 
                		null,
                		CTY, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    /**
     * ---retrieves a particular School(s)---
     * @param city The city to query
     * @return A Cursor object with the results of the query
     * @throws SQLException
     */
    public Cursor getSchool(String city) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, LOCATION_TABLE, new String[] {
                		KEY_ROWID,
                		ADR,
                		CTY + " || ', ' || " + ST + " || ' ' || " + ZIP + " as csz", 
                		PHONE,
            			FAX,
            			EMAIL,
            			WEBSITE,
            			TITLE,
            			MYSCHOOL
                		}, 
                		CTY + " = '" + city + "'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    /**
     * ---retrieves a particular School---
     * @param rowId The school ID to query
     * @return A Cursor object with the results of the query
     * @throws SQLException
     */
    public Cursor getSchool(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, LOCATION_TABLE, new String[] {
                		KEY_ROWID,
                		ADR,
                		CTY + " || ', ' || " + ST + " || ' ' || " + ZIP + " as csz", 
                		PHONE,
            			FAX,
            			EMAIL,
            			WEBSITE,
            			TITLE,
            			MYSCHOOL
                		}, 
                		KEY_ROWID + "=" + rowId, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    /**
     * ---updates a school---
     * @param rowId
     * @param adr
     * @param cty
     * @param st
     * @param zip
     * @param phone
     * @param fax
     * @param email
     * @param website
     * @param title
     * @param myschool
     * @param userid
     * @return
     */
    public boolean updateSchool(long rowId, String adr, String cty, String st, String zip, String phone, String fax,
    		String email, String website, String title, Integer myschool, Integer userid) 
    {
        ContentValues args = new ContentValues();
        args.put(ADR, adr);
        args.put(CTY, cty);
        args.put(ST, st);
        args.put(ZIP, zip);
        args.put(PHONE, phone);
        args.put(FAX, fax);
        args.put(EMAIL, email);
        args.put(WEBSITE, website);
        args.put(TITLE, title);
        args.put(MYSCHOOL, myschool);
        args.put(USERID, userid);
        
        return db.update(LOCATION_TABLE, args, 
                         KEY_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Adds a user to the users table
     *
     * @param user's Flickr Username.
     * @param user's Flickr Real Name.
     * @param NSID is used to identify the user with any operation performed on Flickr.
     */
    public long addUser(String userName, String realName, String nsid) {

        final ContentValues values = new ContentValues();
        values.put(USERNAME, userName);
        values.put(REALNAME, realName);
        values.put(NSID, nsid); 
        
        return db.insert(USERS_TABLE, null, values);
    }
}
